using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using System.Data;
using System.Data.Common;
using NCT.Framework.Database;

namespace VISS.API.Users {
	class UserDA {

		private const string SP_GetAll = "dbo.User_GetAll";
        private const string SP_GetWithPaging = "dbo.User_GetWithPaging";
        private const string SP_GetByUserKey = "dbo.User_GetByUserKey";
        private const string SP_GetByEmail = "dbo.User_GetByEmail";
        private const string SP_Count = "dbo.User_Count";
        private const string SP_CountActived = "dbo.User_CountActived";
        private const string SP_CountByLastLogin = "dbo.User_CountByLastLogin";
		private const string SP_Insert = "dbo.User_Insert";
        private const string SP_ActiveAccount = "dbo.User_ActiveAccount";
		private const string SP_UpdateEmail = "dbo.User_UpdateEmail";
		private const string SP_UpdateLastLogin = "dbo.User_UpdateLastLogin";
		private const string SP_UpdatePassword = "dbo.User_UpdatePassword";
		private const string SP_UpdateSLink = "dbo.User_UpdateSLink";
		private const string SP_UpdateSLinkExpiredDate = "dbo.User_UpdateSLinkExpiredDate";
		private const string SP_UpdateSLink_SLinkExpiredDate = "dbo.User_UpdateSLink-SLinkExpiredDate";
		private const string SP_UpdateStatus = "dbo.User_UpdateStatus";
        private const string SP_AddStatus = "dbo.User_AddStatus";
        private const string SP_RemoveStatus = "dbo.User_RemoveStatus";

        //Use in special case
        private const string SP_ExcuteQueryString = "dbo.User_ExcuteQueryString";

		public static UserEnt CreateFromReader(IDataReader reader) {
			UserEnt item = new UserEnt();
			item.UserKey = ConvertUtils.ToString(reader["UserKey"]);
			item.ID = ConvertUtils.ToLong(reader["ID"]);
			item.Email = ConvertUtils.ToString(reader["Email"]);
			item.Password = ConvertUtils.ToString(reader["Password"]);
			item.Status = (EUserStatus)ConvertUtils.ToShort(reader["Status"]);
			item.SLink = ConvertUtils.ToString(reader["SLink"]);
			item.SLinkExpiredDate = ConvertUtils.ToDateTime(reader["SLinkExpiredDate"]);
			item.DateCreated = ConvertUtils.ToDateTime(reader["DateCreated"]);
			item.LastLogin = ConvertUtils.ToDateTime(reader["LastLogin"]);
			item.DateActivated = ConvertUtils.ToDateTime(reader["DateActivated"]);
			item.DateModified = ConvertUtils.ToDateTime(reader["DateModified"]);
            return item;
		}

        public static List<UserEnt> GetAll(string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			List<UserEnt> result = new List<UserEnt>();
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetAll)) {
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					while (reader.Read()) {
						result.Add(CreateFromReader(reader));
					}
				}
			}
			return result;
		}

        public static List<UserEnt> GetWithPaging(long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserEnt> result = new List<UserEnt>();
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetWithPaging)) {
                db.AddParameter(cmd, "StartRow", startRow);
                db.AddParameter(cmd, "EndRow", endRow);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }

      

        public static UserEnt GetByUserKey(string userKey, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			UserEnt result = null;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_GetByUserKey)) {
                db.AddParameter(cmd, "UserKey", userKey);
				using (IDataReader reader = db.ExecuteReader(cmd)) {
					if (reader.Read()) {
						result = CreateFromReader(reader);
					}
				}
			}
			return result;
		}

        public static UserEnt GetByEmail(string email, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            UserEnt result = null;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetByEmail)) {
                db.AddParameter(cmd, "Email", email);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = CreateFromReader(reader);
                    }
                }
            }
            return result;
        }

        public static long Count(string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_Count)) {
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static long CountActived(string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountActived)) {
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static long CountByLastLogin(DateTime startTime, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            long result = 0;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_CountByLastLogin)) {
                db.AddParameter(cmd, "StartTime", startTime);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToLong(reader["Count"]);
                    }
                }
            }
            return result;
        }

        public static int Insert(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_Insert)) {
				db.AddParameterOuter(cmd, "ID", DbType.Int64);
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "Email", item.Email);
				db.AddParameter(cmd, "Password", item.Password);
				db.AddParameter(cmd, "Status", item.Status);
				db.AddParameter(cmd, "SLink", item.SLink);
				db.AddParameter(cmd, "SLinkExpiredDate", item.SLinkExpiredDate);
				db.AddParameter(cmd, "DateCreated", item.DateCreated);
				db.AddParameter(cmd, "LastLogin", item.LastLogin);
				db.AddParameter(cmd, "DateActivated", item.DateActivated);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                    item.ID = ConvertUtils.ToLong(cmd.Parameters["ID"].Value);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
                    item.ID = ConvertUtils.ToLong(cmd.Parameters["ID"].Value);
				}
			}
			return result;
		}

        public static int ActiveAccount(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_ActiveAccount)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "Status", (short)EUserStatus.Actived);
                db.AddParameter(cmd, "DateActivated", item.DateActivated);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdateEmail(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateEmail)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "Email", item.Email);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdateLastLogin(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateLastLogin)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "LastLogin", item.LastLogin);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdatePassword(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdatePassword)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "Password", item.Password);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdateSLink(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateSLink)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "SLink", item.SLink);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdateSLinkExpiredDate(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateSLinkExpiredDate)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "SLinkExpiredDate", item.SLinkExpiredDate);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdateSLink_SLinkExpiredDate(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateSLink_SLinkExpiredDate)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "SLink", item.SLink);
				db.AddParameter(cmd, "SLinkExpiredDate", item.SLinkExpiredDate);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int UpdateStatus(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
			int result = -1;
			Database db = Database.CreateDatabase(connectionName);
			using (DbCommand cmd = db.CreateStoredCommand(SP_UpdateStatus)) {
				db.AddParameter(cmd, "UserKey", item.UserKey);
				db.AddParameter(cmd, "Status", (short)item.Status);
				db.AddParameter(cmd, "DateModified", item.DateModified);

				if (dbCon != null && dbTrans != null) {
					result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
				}
				else {
					result = db.ExecuteNonQuery(cmd);
				}
			}
			return result;
		}

        public static int AddStatus(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_AddStatus)) {
                db.AddParameter(cmd, "UserKey", item.UserKey);
                db.AddParameter(cmd, "StatusNCT", (short)item.Status);
                db.AddParameter(cmd, "DateModified", item.DateModified);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                }
            }
            return result;
        }

        public static int RemoveStatus(UserEnt item, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = -1;
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_RemoveStatus)) {
                db.AddParameter(cmd, "UserKey", item.UserKey);
                db.AddParameter(cmd, "StatusNCT", (short)item.Status);
                db.AddParameter(cmd, "DateModified", item.DateModified);

                if (dbCon != null && dbTrans != null) {
                    result = db.ExecuteNonQuery(cmd, dbCon, dbTrans);
                }
                else {
                    result = db.ExecuteNonQuery(cmd);
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="queryString"></param>
        /// <param name="connectionName"></param>
        /// <param name="dbCon"></param>
        /// <param name="dbTrans"></param>
        /// <returns></returns>
        public static List<UserEnt> GetByQueryString(string queryString, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserEnt> result = new List<UserEnt>();
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_ExcuteQueryString)) {
                db.AddParameter(cmd, "Sql", queryString);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }
	}
}